package user_service

import (
	e "errors"
	"fmt"
	"io/ioutil"
	"os"
	"path"
	"peilian-api/app/global/common"
	"peilian-api/app/global/databases/tables"
	"peilian-api/app/global/errors"
	"peilian-api/app/global/log"
	"peilian-api/app/global/variable"
	"peilian-api/app/model"
	"peilian-api/utils/encrypt"
	"peilian-api/utils/function"
	"peilian-api/utils/sdk"
	"peilian-api/utils/tools"
	"strings"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/tealeg/xlsx"
)

// 返回值确认
func AddStudent(ctx *gin.Context, ar model.AddStudentRequest) *errors.ErrRes {
	var user tables.User
	user.Profession = ""
	user.Academy = ""
	user.Grade = ""
	uid, exists := ctx.Get("uid")
	if !exists {
		return errors.NewErrInfo(errors.SessionEmptyCode, fmt.Errorf("请重新登录，然后再试"))
	}
	us, _ := tables.GetAgentInfoByUid(uid.(uint))
	var school = us.School // 学校不可能为空
	var password = ""
	phone := ar.Phone
	sno := ar.Sno
	if phone == "" && sno == "" {
		return errors.NewErrInfo(30000, fmt.Errorf("手机号或者学号不能为空"))
	}
	if phone != "" {
		// 校验手机号
		if !tools.CheckMobile(phone) {
			return errors.NewErrInfo(30000, fmt.Errorf("手机号格式不正确"))
		}
		// 判断该用户是否存在
		ues, exists, _ := tables.GetUserInfoByPhone(ar.Phone)
		if exists {
			var userType string = string(ues.Type)
			if userType == tables.UserTypeAdmin {
				return errors.NewErrInfo(errors.PublicErrorCode, fmt.Errorf("您已经是管理员了，不能成为学生用户~"))
			}
			if userType == tables.UserTypeNormal {
				return errors.NewErrInfo(errors.PublicErrorCode, fmt.Errorf("该手机号已经被绑定~"))
			}
			if userType == tables.UserTypeAnonymous {
				// 查询新的记录 更新原有数据
				_, err := tables.DeleteUserChangeById(int(ues.ID))
				if err != nil {
					return errors.NewErrInfo(errors.PublicErrorCode, err)
				}
			}
		}
	}
	if sno != "" {
		ues, exists, _ := tables.GetUserInfoBySchoolAbdSno(school, sno)
		if exists {
			var userType string = string(ues.Type)
			if userType == tables.UserTypeAdmin {
				return errors.NewErrInfo(errors.PublicErrorCode, fmt.Errorf("该账号是管理员账号~"))
			}
			if userType == tables.UserTypeNormal {
				return errors.NewErrInfo(errors.PublicErrorCode, fmt.Errorf("该学生已经存在~"))
			}
			if userType == tables.UserTypeAnonymous {
				// 查询新的记录 更新原有数据
				_, err := tables.DeleteUserChangeById(int(ues.ID))
				if err != nil {
					return errors.NewErrInfo(errors.PublicErrorCode, err)
				}
			}
		}
		// 生成默认密码
		password = generatePwd(ctx, school, sno)
	}
	// 根据用户名判断
	// ues2, exists2, _ := tables.GetUserInfoByUserName(ar.Name)
	// if exists2 {
	// 	var userType string = string(ues2.Type)
	// 	if userType == tables.UserTypeNormal || userType == tables.UserTypeAnonymous {
	// 		return fmt.Errorf("该用户名已经被占用~")
	// 	}
	// }

	// 如果未选择年级、学院和专业，则根据学校去查询信息 否则，根据constitute_id查询结果
	if ar.ConstituteId == 0 {
		var cs tables.Constitute
		variable.DB.MySQL.Where("name = ? and type = 'school'", school).First(&cs)
		if cs.ID == 0 {
			return errors.NewErrInfo(errors.PublicErrorCode, fmt.Errorf("请重新登录，然后再试~"))
		}
		ar.ConstituteId = cs.ID
	} else {
		// 该值传入的数据是年级、学院、专业
		c, _ := tables.GetSecondLevelById(int(ar.ConstituteId))
		// 如果是年级，则一定有学院
		if c.Type == tables.GradeType {
			user.Grade = c.Name
			user.Profession = c.BelongTo.Name
			c2, _ := tables.GetSecondLevelById(int(c.BelongTo.ID))
			if c2.Type == tables.ProfessionType {
				user.Academy = c2.BelongTo.Name
			}
		} else if c.Type == tables.ProfessionType {
			user.Profession = c.Name
			user.Academy = c.BelongTo.Name
		} else if c.Type == tables.AcademyType {
			user.Academy = c.Name
		}
	}

	user.Password = password // 更新密码
	user.Sno = sno           // 更新学号
	user.School = school     // 更新学校
	user.Name = ar.Name
	user.Phone = ar.Phone
	user.Type = tables.UserTypeNormal // 统一normal
	user.ConstituteID = ar.ConstituteId
	user.Level = tables.RolesLevel0 // 普通权限
	user.AgentID = us.AgentID
	user.Education = ar.Education
	user.Grade = ar.Grade
	// 添加用户
	d, err := tables.AddUser(user)
	go func() {
		defer tools.RecoverGoroutine()
		new(tables.OptionLog).InsertOptionLog(ctx, d, err)
	}()

	if d == 0 {
		return errors.NewErrInfo(errors.PublicErrorCode, fmt.Errorf(errors.DatabaseMySqlOptionFail))
	}
	return nil
}

func UpdateStudent(ctx *gin.Context, id int, ur model.UpdateStudentRequest) error {
	// 首先判断该用户是否存在
	user, _ := tables.GetUserInfoById(id)
	m := make(map[string]interface{})
	m["sno"] = ur.Sno
	m["name"] = ur.Name
	m["phone"] = ur.Phone
	m["education"] = ur.Education
	m["grade"] = ur.Grade
	m["constitute_id"] = ur.ConstituteId
	m["profession"] = user.Profession
	m["academy"] = user.Academy

	// 查询除 该用户外 该手机号是否被使用过
	u := []tables.User{}
	var countNum int64 = 0
	if ur.Phone != "" {
		variable.DB.MySQL.Where("id != ? and phone = ? and type in (?,?)", id, ur.Phone, tables.UserTypeAnonymous, tables.UserTypeNormal).Find(&u).Count(&countNum)
		if countNum != 0 {
			return fmt.Errorf("手机号已经被添加~")
		}
	}
	// 判断学号是否被绑定过
	if ur.Sno != "" {
		variable.DB.MySQL.Where("id != ? and school = ? and sno = ? and type in (?,?)", id, user.School, ur.Sno, tables.UserTypeAnonymous, tables.UserTypeNormal).Find(&u).Count(&countNum)
		if countNum != 0 {
			return fmt.Errorf("学号已经被添加~")
		}
		// 判断user是否设置密码, // 生成默认密码
		if len(user.Password) == 0 {
			password := generatePwd(ctx, user.School, ur.Sno)
			m["password"] = password
		}
	}
	// 如果未选择学院和专业，则根据学校去查询信息 否则，根据constitute_id查询结果
	if ur.ConstituteId == 0 {
		var cs tables.Constitute
		var school = user.School
		variable.DB.MySQL.Where("name = ? and type = 'school'", school).First(&cs)
		m["constitute_id"] = cs.ID
	} else {
		// 该值传入的数据是年级、学院、专业
		c, _ := tables.GetThirdLevelById(int(ur.ConstituteId))
		if c.Type == tables.GradeType { // 如果是年级，则一定有 专业-学院
			c, _ := tables.GetThirdLevelById(int(*(c.BelongToID)))
			if c.Type == tables.ProfessionType {
				m["profession"] = c.Name
				m["academy"] = c.BelongTo.Name
			}
		} else if c.Type == tables.ProfessionType { // 如果是专业 则一定有学院
			m["profession"] = c.Name
			m["academy"] = c.BelongTo.Name
		} else if c.Type == tables.AcademyType {
			m["academy"] = c.Name
		}
	}
	// 更改是否可以使用小程序
	if ur.CanUseApplets != nil {
		// 查询agent 的总数 和时间
		if *ur.CanUseApplets == true && user.UseAppletsEndTime.Before(time.Now()) {
			agent, err := tables.GetAgentById(user.AgentID)
			if err != nil {
				return err
			}
			// 查询现在开通的总数
			var count int64
			err = variable.DB.MySQL.Model(tables.User{}).Where(
				"use_applets_end_time > ? and agent_id = ?", time.Now(), agent.ID).Count(&count).Error
			if err != nil {
				return err
			}
			if int(count) < agent.AppletsConcurrent {
				m["use_applets_end_time"] = time.Now().AddDate(0, 0, agent.AppletsUseDays)
			} else {
				fmt.Println("Applets Concurrent user full")
				log.Logger.InfoF(ctx, fmt.Sprintf("Applets Concurrent user full :%d", count))
				return e.New("applets Concurrent user full")
			}
		} else {
			m["use_applets_end_time"] = time.Now().AddDate(-time.Now().Year(), 0, 0)
		}
	}

	// 根据id 更新数据
	d, err := tables.UpdateUserInfoMapById(m, id)
	// 记录日志
	go func() {
		defer tools.RecoverGoroutine()
		new(tables.OptionLog).InsertOptionLog(ctx, d, err)
	}()
	return err
}

// 删除信息ID
func DeleteByIdStudent(ctx *gin.Context, id int) error {
	d, err := tables.DeleteUserById(id)

	// 记录日志
	go func() {
		defer tools.RecoverGoroutine()
		new(tables.OptionLog).InsertOptionLog(ctx, d, err)
	}()

	return err
}

// 生成密码
func generatePwd(ctx *gin.Context, school string, sno string) string {
	// 1. 根据学校名称获取学校前缀
	var prefix = "YS"
	cc, _ := new(tables.CommonConf).FetchByWhereStr(fmt.Sprintf("conf_key = '%s' and remarks = '%s'", tables.ConfKeySchool, school))
	if len(cc) != 0 {
		prefix = cc[0].ConfValue
	}
	// 2. 生成密码
	// rsaPwd, _ := encrypt.RsaEncode(prefix + sno)
	pwd, _ := encrypt.EncryptPassword(prefix + sno)
	return pwd
}

// 重置学生密码
func ResetStudentPwd(ctx *gin.Context, rsp model.ResetStudentPwdRequest) *errors.ErrRes {
	uid := rsp.Id
	school := rsp.School
	sno := rsp.Sno
	pwd := generatePwd(ctx, school, sno)
	// 3. 更新密码
	var whereMap map[string]interface{}
	whereMap = make(map[string]interface{}, 0)
	whereMap["password"] = pwd
	_, err := tables.UpdateMapUserInfoById(whereMap, uid)
	if err != nil {
		return errors.NewErrInfo(30001, fmt.Errorf("pwd reset error"))
	}
	// 删除redis
	user, _ := tables.GetUserInfoById(uid)
	// 清除token重新登录
	// if len(user.Sno) != 0 {
	// 	redisSnoKey := fmt.Sprintf(common.RedisPhoneKey, fmt.Sprintf("%s_%s", user.School, user.Sno))
	// 	variable.DB.Redis.Del(redisSnoKey)
	// }
	// if len(user.Phone) != 0 {
	// 	redisPhoneKey := fmt.Sprintf(common.RedisPhoneKey, user.Phone)
	// 	variable.DB.Redis.Del(redisPhoneKey)
	// }
	redisUserLoginKey := fmt.Sprintf(common.RedisUserLoginKey, user.ID)
	variable.DB.Redis.Del(redisUserLoginKey)
	_, er := SetUserInfo(ctx, user.ID)
	if er != nil {
		return er
	}
	return nil
}

// 获取用户信息
func GetUserInfoStudent(ctx *gin.Context, gr model.GetStudentRequest) (count int64, out []model.StudentResp, err error) {
	// 获取当前用户的用户名
	uid, exists := ctx.Get("uid")
	if !exists {
		return 0, out, fmt.Errorf("操作失败~")
	}
	us, _ := tables.GetAgentInfoByUid(uid.(uint))
	level := us.Level
	consId := us.ConstituteID

	var user tables.User
	var userList []tables.User
	var query = gr.Query
	var cid = gr.ConstituteId
	var education = gr.Education
	var schoolId = gr.School
	qlen := len(query)
	// 拼接where条件
	var whereMap map[string]interface{} = make(map[string]interface{}) // 创建map集合
	whereMap["type"] = tables.UserTypeNormal
	if us.Type == tables.UserTypeSadmin {
		if schoolId != 0 && schoolId != 1 {
			ci, _ := new(tables.Constitute).GetConstituteById(schoolId)
			whereMap["school"] = ci.Name
		} else if schoolId == 1 { // 特定的游客大学
			whereMap["type"] = tables.UserTypeAnonymous
		}
	} else {
		whereMap["school"] = us.School
	}
	if cid > 0 {
		whereMap["constitute_id"] = cid
	}
	if education != "" {
		whereMap["education"] = education
	}

	if level == tables.RolesLevel3 {
		ci, _ := new(tables.Constitute).GetupConfigMap(consId)
		whereMap["academy"] = ci[tables.AcademyType].Name
		if gr.Profession != 0 {
			ci, _ := new(tables.Constitute).GetConstituteById(gr.Profession)
			whereMap["profession"] = ci.Name
		}
	} else if level == tables.RolesLevel4 {
		ci, _ := new(tables.Constitute).GetupConfigMap(consId)
		whereMap["academy"] = ci[tables.AcademyType].Name
		whereMap["profession"] = ci[tables.ProfessionType].Name
	} else {
		if gr.Academy != 0 {
			var am tables.Constitute
			variable.DB.MySQL.Where("id = ?", gr.Academy).Find(&am)
			whereMap["academy"] = am.Name
		}
		if gr.Profession != 0 {
			var pf tables.Constitute
			variable.DB.MySQL.Where("id = ?", gr.Profession).Find(&pf)
			whereMap["profession"] = pf.Name
		}
	}

	if gr.Grade != "全部年级" && gr.Grade != "" && gr.Grade != "0" {
		whereMap["grade"] = gr.Grade
	}

	var page = gr.Page
	var pageSize = gr.PageSize
	var trainSort = gr.TrainSort
	// 查询所有数据
	if qlen == 0 {
		if trainSort == "desc" {
			d := variable.DB.MySQL.Where(whereMap).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else if trainSort == "asc" {
			d := variable.DB.MySQL.Where(whereMap).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count asc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else {
			d := variable.DB.MySQL.Where(whereMap).Limit(pageSize).Offset((page - 1) * pageSize).Order("created_at desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		}
		d2 := variable.DB.MySQL.Model(&user).Where(whereMap).Count(&count)
		if d2.Error != nil {
			return 0, out, fmt.Errorf("查询失败~")
		}
	} else {
		query = "%" + query + "%"
		if trainSort == "desc" {
			d := variable.DB.MySQL.Where(whereMap).Where("name like ? or phone like ? or sno like ?", query, query, query).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else if trainSort == "asc" {
			d := variable.DB.MySQL.Where(whereMap).Where("name like ? or phone like ? or sno like ?", query, query, query).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count asc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else {
			d := variable.DB.MySQL.Where(whereMap).Where("name like ? or phone like ? or sno like ?", query, query, query).Limit(pageSize).Offset((page - 1) * pageSize).Order("created_at desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		}
		d2 := variable.DB.MySQL.Model(&user).Where(whereMap).Where("name like ? or phone like ? or sno like ?", query, query, query).Count(&count)
		if d2.Error != nil {
			return 0, out, fmt.Errorf("查询失败~")
		}
	}

	// 整理返回数据
	for i := 0; i < len(userList); i++ {
		var mr model.StudentResp
		mr.Id = userList[i].ID
		mr.Name = userList[i].Name
		mr.Phone = userList[i].Phone
		mr.School = userList[i].School
		mr.Academy = userList[i].Academy
		mr.Profession = userList[i].Profession
		mr.Grade = userList[i].Grade
		mr.Academy = userList[i].Academy
		mr.Profession = userList[i].Profession
		mr.TrainCount = userList[i].TrainCount
		mr.ConstituteId = userList[i].ConstituteID
		mr.Education = userList[i].Education
		mr.Sno = userList[i].Sno
		if userList[i].UseAppletsEndTime.After(time.Now()) {
			mr.CanUseApplets = true
		}
		if userList[i].IsMember != tables.MemberStatusDefault { // 会员生效
			// 获取用户的会员状态
			m, _ := new(tables.Member).GetMemeberByUid(int64(userList[i].ID))
			// 组装数据
			var memeberInfo model.UserMember
			if m.ID != 0 {
				// 计算过期时间
				daySum, _ := tools.GetDaysBetween2Date("2006-01-02 15:04:05", m.MemberExpire, time.Now().UTC().Format("2006-01-02 15:04:05"))
				memeberInfo.MemberExpire = m.MemberExpire
				memeberInfo.MemberLevel = 0
				memeberInfo.MemeberTitle = m.Title
				memeberInfo.MemeberStartTime = m.MemberStartTime
				memeberInfo.MemberStatus = true
				memeberInfo.SurplusTime = daySum
				if daySum+1 <= 0 {
					memeberInfo.MemberStatus = false
					memeberInfo.SurplusTime = 0
				}
			}
			mr.Member = &memeberInfo
		}
		out = append(out, mr)
	}
	return count, out, nil
}

type GetStudentReq struct {
	Page     int `json:"page" form:"page"`
	PageSize int `json:"page_size" form:"page_size"`
}

// 获取用户信息(外部使用)
func GetUserStudentInfo(ctx *gin.Context, gr GetStudentReq) (count int64, out []model.StudentResp, err error) {
	// 获取当前用户的用户名
	aid, exists := ctx.Get("aid")
	if !exists {
		return 0, out, fmt.Errorf("操作失败~")
	}
	var user tables.User
	var userList []tables.User
	// 拼接where条件
	var whereMap = make(map[string]interface{}) // 创建map集合
	whereMap["type"] = tables.UserTypeNormal
	whereMap["agent_id"] = aid

	var page = gr.Page
	var pageSize = gr.PageSize
	// 查询所有数据

	d := variable.DB.MySQL.Where(whereMap).Limit(pageSize).Offset((page - 1) * pageSize).Order("created_at desc").Find(&userList)
	if d.Error != nil {
		return 0, out, fmt.Errorf("查询失败~")
	}
	d2 := variable.DB.MySQL.Model(&user).Where(whereMap).Count(&count)
	if d2.Error != nil {
		return 0, out, fmt.Errorf("查询失败~")
	}

	// 整理返回数据
	for i := 0; i < len(userList); i++ {
		var mr model.StudentResp
		mr.Id = userList[i].ID
		mr.Name = userList[i].Name
		mr.Phone = userList[i].Phone
		mr.School = userList[i].School
		mr.Academy = userList[i].Academy
		mr.Profession = userList[i].Profession
		mr.Grade = userList[i].Grade
		mr.Academy = userList[i].Academy
		mr.Profession = userList[i].Profession
		mr.TrainCount = userList[i].TrainCount
		mr.ConstituteId = userList[i].ConstituteID
		mr.Education = userList[i].Education
		mr.Sno = userList[i].Sno
		if userList[i].UseAppletsEndTime.After(time.Now()) {
			mr.CanUseApplets = true
		}
		if userList[i].IsMember != tables.MemberStatusDefault { // 会员生效
			// 获取用户的会员状态
			m, _ := new(tables.Member).GetMemeberByUid(int64(userList[i].ID))
			// 组装数据
			var memeberInfo model.UserMember
			if m.ID != 0 {
				// 计算过期时间
				daySum, _ := tools.GetDaysBetween2Date("2006-01-02 15:04:05", m.MemberExpire, time.Now().UTC().Format("2006-01-02 15:04:05"))
				memeberInfo.MemberExpire = m.MemberExpire
				memeberInfo.MemberLevel = 0
				memeberInfo.MemeberTitle = m.Title
				memeberInfo.MemeberStartTime = m.MemberStartTime
				memeberInfo.MemberStatus = true
				memeberInfo.SurplusTime = daySum
				if daySum+1 <= 0 {
					memeberInfo.MemberStatus = false
					memeberInfo.SurplusTime = 0
				}
			}
			mr.Member = &memeberInfo
		}
		out = append(out, mr)
	}
	return count, out, nil
}

// 获取游客信息 -- 只有超级管理员可以获取到数据
func GetUserInfoStudentY(ctx *gin.Context, gr model.GetStudentYRequest) (count int64, out []model.StudentResp, err error) {
	// 获取当前用户的用户名
	uid, exists := ctx.Get("uid")
	if !exists {
		return 0, out, fmt.Errorf("操作失败~")
	}
	us, _ := tables.GetAgentInfoByUid(uid.(uint))
	if tables.UserTypeSadmin != us.Type {
		return 0, out, fmt.Errorf("您无权限查看数据")
	}
	var user tables.User
	var userList []tables.User
	var query = gr.Query
	var memberStatus = gr.MemberStatus
	qlen := len(query)
	// 拼接where条件
	var whereStr = fmt.Sprintf("type = '%s'", tables.UserTypeAnonymous)
	if memberStatus != 2 { // 全部
		whereStr = whereStr + fmt.Sprintf(" and is_member = %d", memberStatus)
	}
	var page = gr.Page
	var pageSize = gr.PageSize
	var trainSort = gr.TrainSort
	var uids []string
	if value, ok := common.MemberTypeMap[gr.MemberType]; ok {
		variable.DB.MySQL.Model(&tables.Member{}).Where("title like ?", "%"+value+"%").Select("uid").Find(&uids)
		if len(uids) == 0 { // 数据拼接上去
			return 0, out, nil
		}
		ids := strings.Join(uids, ",")
		whereStr = whereStr + fmt.Sprintf(" and id in (%s) and is_member = 1", ids) // 一定是会员开通状态

	}
	fmt.Println(whereStr)
	// 查询所有数据
	if qlen == 0 {
		if trainSort == "desc" {
			d := variable.DB.MySQL.Where(whereStr).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else if trainSort == "asc" {
			d := variable.DB.MySQL.Where(whereStr).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count asc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else {
			d := variable.DB.MySQL.Where(whereStr).Limit(pageSize).Offset((page - 1) * pageSize).Order("created_at desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		}
		d2 := variable.DB.MySQL.Model(&user).Where(whereStr).Count(&count)
		if d2.Error != nil {
			return 0, out, fmt.Errorf("查询失败~")
		}
	} else {
		query = "%" + query + "%"
		if trainSort == "desc" {
			d := variable.DB.MySQL.Where(whereStr).Where("name like ? or phone like ? or sno like ?", query, query, query).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else if trainSort == "asc" {
			d := variable.DB.MySQL.Where(whereStr).Where("name like ? or phone like ? or sno like ?", query, query, query).Limit(pageSize).Offset((page - 1) * pageSize).Order("train_count asc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		} else {
			d := variable.DB.MySQL.Where(whereStr).Where("name like ? or phone like ? or sno like ?", query, query, query).Limit(pageSize).Offset((page - 1) * pageSize).Order("created_at desc").Find(&userList)
			if d.Error != nil {
				return 0, out, fmt.Errorf("查询失败~")
			}
		}
		d2 := variable.DB.MySQL.Model(&user).Where(whereStr).Where("name like ? or phone like ? or sno like ?", query, query, query).Count(&count)
		if d2.Error != nil {
			return 0, out, fmt.Errorf("查询失败~")
		}
	}

	// 整理返回数据
	for i := 0; i < len(userList); i++ {
		var mr model.StudentResp
		mr.Id = userList[i].ID
		mr.Name = userList[i].Name
		mr.Phone = userList[i].Phone
		mr.School = userList[i].School
		mr.Academy = userList[i].Academy
		mr.Profession = userList[i].Profession
		mr.Grade = userList[i].Grade
		mr.Academy = userList[i].Academy
		mr.Profession = userList[i].Profession
		mr.TrainCount = userList[i].TrainCount
		mr.ConstituteId = userList[i].ConstituteID
		mr.Education = userList[i].Education
		mr.Sno = userList[i].Sno
		if userList[i].UseAppletsEndTime.After(time.Now()) {
			mr.CanUseApplets = true
		}
		if userList[i].IsMember != tables.MemberStatusDefault { // 会员生效
			// 获取用户的会员状态
			m, _ := new(tables.Member).GetMemeberByUid(int64(userList[i].ID))
			// 组装数据
			var memeberInfo model.UserMember
			if m.ID != 0 {
				// 计算过期时间
				daySum, _ := tools.GetDaysBetween2Date("2006-01-02 15:04:05", m.MemberExpire, time.Now().UTC().Format("2006-01-02 15:04:05"))
				memeberInfo.MemberExpire = m.MemberExpire
				memeberInfo.MemberLevel = 0
				memeberInfo.MemeberTitle = m.Title
				memeberInfo.MemeberStartTime = m.MemberStartTime
				memeberInfo.MemberStatus = true
				memeberInfo.SurplusTime = daySum
				if daySum+1 <= 0 {
					memeberInfo.MemberStatus = false
					memeberInfo.SurplusTime = 0
				}
			}
			mr.Member = &memeberInfo
		}
		out = append(out, mr)
	}
	return count, out, nil
}

// 导入数据
func ImportStudentInfo(ctx *gin.Context) (int, int, int, string, error) {
	// 获取当前用户的用户名+学校
	uid, exists := ctx.Get("uid")
	if !exists {
		return 0, 0, 0, "", fmt.Errorf("操作失败，请联系管理员~")
	}
	us, _ := tables.GetAgentInfoByUid(uid.(uint))
	school := us.School
	_, f, err := ctx.Request.FormFile("file")
	if err != nil {
		return 0, 0, 0, "", fmt.Errorf("文件获取失败, " + err.Error())
	}
	if !tools.CheckExcleName(f.Filename) {
		return 0, 0, 0, "", fmt.Errorf("不能识别文件，请上传.xlsx格式的文件")
	}

	f2, err := f.Open()
	if err != nil {
		return 0, 0, 0, "", fmt.Errorf("文件打开异常, " + err.Error())
	}
	b, _ := ioutil.ReadAll(f2)

	// 文件上传到us3并创建记录
	fileExt := strings.ToLower(path.Ext(f.Filename))
	fileName := function.CreateMD5(fmt.Sprintf("%s%s", f.Filename, time.Now().String()))
	filePath := common.FilePath + fileName + fileExt
	go func() {
		defer tools.RecoverGoroutine()
		savePath := variable.Config.UfsInfo.BucketPrefix + filePath
		if err := sdk.IOput(b, savePath, sdk.XlsxMetaType); err != nil {
			log.Logger.InfoF(ctx, "文件上传失败"+savePath)
		}
	}()
	uur := tables.UserUploadRecord{UserID: uid.(uint), OriginFile: filePath}
	row, err := tables.AddRecord(&uur)
	if err != nil || row == 0 {
		return 0, 0, 0, "", fmt.Errorf("操作数据库错误~")
	}
	// 读取文件
	f3, err := xlsx.OpenBinary(b)
	if err != nil {
		return 0, 0, 0, "", fmt.Errorf("文件读取异常, " + err.Error())
	}
	dataArr, err := f3.ToSlice()
	if err != nil {
		return 0, 0, 0, "", fmt.Errorf("文件内容格式异常, " + err.Error())
	}

	// 数据预处理
	var out [][][]string = make([][][]string, len(dataArr)-1)
	for i := 0; i < len(dataArr); i++ {
		var data [][]string
		for j := 0; j < len(dataArr[i]); j++ {
			var arr []string
			if len(dataArr[i][j]) == 0 {
				continue
			}
			if len(dataArr[i][j][0]) == 0 {
				continue
			}
			for _, v := range dataArr[i][j] {
				arr = append(arr, strings.TrimSpace(v))
			}
			data = append(data, arr)
		}
		out = append(out, data)
	}

	sum := len(out[0]) - 1
	if sum >= 5000 { // 文件最大为5000条
		return 0, 0, 0, "", fmt.Errorf("上传文件数量>=5000条")
	}

	// 校验表头
	if !tools.CheckHeader(out, model.Stu{}) {
		return 0, 0, 0, "", fmt.Errorf("模板不符,l.请下载模板Excel")
	}
	// 学校-学院-专业-年级 顺序
	cons, err := tables.GetInfoByType(1, school)
	if err != nil {
		return 0, 0, 0, "", fmt.Errorf("操作异常，请重试~")
	}
	mp := tables.AssembleGivenData(cons)

	// 对数做去重处理
	var snos []string
	for _, row := range out {
		for i := 1; i < len(row); i++ {
			if len(row[i][1]) != 0 {
				snos = append(snos, row[i][1])
			}
		}
	}
	var phones []string
	for _, row := range out {
		for i := 1; i < len(row); i++ {
			if len(row[i][2]) != 0 {
				phones = append(phones, row[i][2])
			}
		}
	}

	// 根据学号查询所有用户
	var where = map[string]interface{}{"school": school, "sno": snos, "type": "normal"}
	snoUsers, _ := new(tables.User).GetUsersByWhere(where, "sno")

	// 查询该学校下的所有用户
	where = map[string]interface{}{"phone": phones, "type": "normal", "school": school}
	normalUsers, _ := new(tables.User).GetUsersByWhere(where, "phone")
	where["type"] = "anonymous"
	anonymousUsers, _ := new(tables.User).GetUsersByWhere(where, "phone")

	var normalSno = make(map[string]string)
	var normalPhone = make(map[string]string)
	var anonymousPhone = make(map[string]string)
	for _, v := range snoUsers {
		normalSno[v.Sno] = v.Sno
	}
	for _, v := range normalUsers {
		normalPhone[v.Phone] = v.Phone
	}
	for _, v := range anonymousUsers {
		anonymousPhone[v.Phone] = v.Phone
	}

	var errs []interface{}
	var stus []model.Stu
	var upts []model.Stu
	var repeatPhoneStu []string // 去重处理
	var repeatSnoStu []string
	for _, row := range out {
		for i := 1; i < len(row); i++ {
			if len(row[i][1]) == 0 && len(row[i][1]) == 2 {
				continue
			}
			// 校验姓名
			var stu = model.Stu{
				Name:         row[i][0],
				Sno:          row[i][1],
				Phone:        row[i][2],
				Academy:      row[i][3],
				Profession:   row[i][4],
				Education:    row[i][5],
				Grade:        row[i][6],
				ConstituteID: mp[school][row[i][3]][row[i][4]],
				ErrInfo:      ""}
			if len(row[i][0]) == 0 {
				stu.ErrInfo = "姓名不能为空"
			} else if len(stu.Sno) == 0 && len(stu.Phone) == 0 {
				stu.ErrInfo = "手机号或者学号不能为空"
			} else if _, exist := normalSno[stu.Sno]; exist && len(stu.Sno) != 0 { // 学号不为空
				stu.ErrInfo = "该学号已经存在"
			}
			if len(stu.Phone) != 0 { // 手机号不为空
				if !tools.CheckMobile(stu.Phone) {
					stu.ErrInfo = "手机号格式校验错误"
				} else if _, exist := normalPhone[stu.Phone]; exist {
					stu.ErrInfo = "表内有重复数据，手机号作为唯一识别标识"
				}
			}
			if mp[school][stu.Academy][stu.Profession] == 0 {
				stu.ErrInfo = "该学院-专业-年级不存在，请联系管理员添加"
			} else if !tools.Contains(common.EducationArr, stu.Education) {
				stu.ErrInfo = fmt.Sprintf("该学历不存在,学历仅限:%s", tools.ArraysToStr(common.EducationArr, "-"))
			} else if !tools.Contains(common.GradeArr, stu.Grade) {
				stu.ErrInfo = fmt.Sprintf("该年级不存在，年级仅限:%s", tools.ArraysToStr(common.GradeArr, "-"))
			}
			// 判断更新还是插入
			_, existPhone := anonymousPhone[stu.Phone]
			_, existSno := normalSno[stu.Sno]
			if existPhone || existSno {
				upts = append(upts, stu)
				continue
			}
			if tools.Contains(repeatPhoneStu, stu.Phone) { // 手机号
				continue
			}
			if tools.Contains(repeatSnoStu, stu.Phone) { // 学号
				continue
			}
			if len(stu.ErrInfo) == 0 { // 数据插入
				stus = append(stus, stu)
				if len(stu.Phone) != 0 {
					repeatPhoneStu = append(repeatPhoneStu, stu.Phone)
				}
				if len(stu.Sno) != 0 {
					repeatSnoStu = append(repeatSnoStu, stu.Sno)
				}
				continue
			} else {
				errs = append(errs, stu)
			}
		}
	}

	// 插入数据
	var users []tables.User
	for i := 0; i < len(stus); i++ {
		users = append(users, tables.User{
			Sno:          stus[i].Sno,
			Name:         stus[i].Name,
			Password:     generatePwd(ctx, school, stus[i].Sno),
			School:       school,
			Academy:      stus[i].Academy,
			Profession:   stus[i].Profession,
			Grade:        stus[i].Grade,
			Education:    stus[i].Education,
			Phone:        stus[i].Phone,
			ConstituteID: stus[i].ConstituteID,
			Type:         tables.UserTypeNormal,
			Level:        tables.RolesLevel0,
			AgentID:      us.AgentID,
		})
	}
	sucNum := 0
	errNum := 0
	if len(users) != 0 {
		var batchUser []tables.User
		var count = 0
		for i := 0; i < len(users); i++ {
			batchUser = append(batchUser, users[i])
			count++
			if count == 100 {
				batchNum, _ := tables.BatchAddUser(batchUser, len(batchUser))
				sucNum = sucNum + int(batchNum)
				if int(batchNum) != len(batchUser) {
					for i := 0; i < len(batchUser); i++ {
						errs = append(errs, model.Stu{
							Sno:        batchUser[i].Sno,
							Name:       batchUser[i].Name,
							Phone:      batchUser[i].Phone,
							Academy:    batchUser[i].Academy,
							Profession: batchUser[i].Profession,
							Education:  batchUser[i].Education,
							Grade:      batchUser[i].Grade,
							ErrInfo:    "插入失败，联系管理员",
						})
					}
				}
				count = 0
				batchUser = []tables.User{}
			}

		}
		if len(batchUser) != 0 {
			batchNum, _ := tables.BatchAddUser(batchUser, len(batchUser))
			sucNum = sucNum + int(batchNum)
			if int(batchNum) != len(batchUser) {
				for i := 0; i < len(batchUser); i++ {
					errs = append(errs, model.Stu{
						Sno:        batchUser[i].Sno,
						Name:       batchUser[i].Name,
						Phone:      batchUser[i].Phone,
						Academy:    batchUser[i].Academy,
						Profession: batchUser[i].Profession,
						Education:  batchUser[i].Education,
						Grade:      batchUser[i].Grade,
						ErrInfo:    "插入失败，联系管理员",
					})
				}

			}
		}
	}

	// 更新
	for i := 0; i < len(upts); i++ {
		where := map[string]interface{}{"type": tables.UserTypeAnonymous, "phone": upts[i].Phone}
		uptField := map[string]interface{}{
			"sno":           upts[i].Sno,
			"password":      generatePwd(ctx, school, upts[i].Sno),
			"name":          upts[i].Name,
			"school":        school,
			"academy":       upts[i].Academy,
			"profession":    upts[i].Profession,
			"grade":         upts[i].Grade,
			"education":     upts[i].Education,
			"constitute_id": upts[i].ConstituteID,
			"type":          tables.UserTypeNormal,
			"level":         tables.RolesLevel1,
			"is_change":     1,
			"agent_id":      us.AgentID,
		}
		if _, err := new(tables.User).UpdateUserInfoByWhere(uptField, where); err != nil {
			upts[i].ErrInfo = "更新失败"
			errs = append(errs, upts[i])
			continue
		}
		sucNum++
	}
	// 将文件更新到数据库
	if len(errs) != 0 {
		dir, _ := os.Getwd()
		fileName = fileName + "_error.xlsx"
		localPath := dir + "/logs/" + fileName
		if _, err := tools.SaveExcle(localPath, "sheet1", common.ErrStuHeader, errs); err != nil {
			log.Logger.InfoF(ctx, "文件保存失败"+fileName)
		} else {
			filePath = common.FilePath + fileName
			sdk.FilePut(localPath, variable.Config.UfsInfo.BucketPrefix+filePath, sdk.XlsxMetaType)
			_, err = tables.UpdateRecordByWhere(map[string]interface{}{"error_file": filePath}, map[string]interface{}{"id": uur.ID})
			if err != nil {
				log.Logger.InfoF(ctx, "导入错误文件报错失败:"+err.Error())
			}
		}
		os.Remove(fileName)

		// 记录日志
		go func() {
			defer tools.RecoverGoroutine()
			new(tables.OptionLog).InsertOptionLog(ctx, int64(sucNum), err)
		}()

		return sum, sucNum, len(errs), variable.Config.UfsInfo.UfsUrl + variable.Config.UfsInfo.BucketPrefix + filePath, nil
	}
	// if errNum != 0 {
	// 	return sum, sucNum, len(errs), variable.Config.UfsInfo.UfsUrl + variable.Config.UfsInfo.BucketPrefix + filePath, nil
	// }
	return sum, sucNum, errNum, "", nil
}

// 判断服务是否过期
func JudgeService(ctx *gin.Context) (bool, error) {
	uid, exists := ctx.Get("uid")
	if !exists {
		return true, fmt.Errorf("用户不存在~")
	}
	us, _ := tables.GetAgentInfoByUid(uid.(uint))
	if us.Type == tables.UserTypeNormal {
		sc := variable.DB.Redis.Get(fmt.Sprintf(common.RedisAccountCountKey, us.AgentID))
		str, err := sc.Result()
		if err != nil {
			return true, nil
		}
		diffTime, _ := tools.GetDaysBetween2Date("2006-01-02", str, time.Now().UTC().Format("2006-01-02"))
		if diffTime < 0 {
			return true, nil
		}
	}
	return false, nil
}
